﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using Aliyeye.Car.Net.UI.SystemHelp;
using System.Data;

namespace Aliyeye.Car.Net.UI.PrintExport
{
    public class ExcelHelper
    {
        object readOnly = false;
        object myTrue = true;
        object myFalse = false;
        object missingValue = Type.Missing;
        object range = Microsoft.Office.Interop.Word.WdPrintOutRange.wdPrintCurrentPage;
        object items = Microsoft.Office.Interop.Word.WdPrintOutItem.wdPrintDocumentContent;
        object copies = "1";
        object pages = "1";
        object pageType = Microsoft.Office.Interop.Word.WdPrintOutPages.wdPrintAllPages;

        object oMissing = System.Reflection.Missing.Value;

        object missing = System.Reflection.Missing.Value;

        public ExcelHelper()
        {
            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
        }

        /// <summary>
        /// 导出提醒清单
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool RemindListExport(CarSvc.RemindListInfo[] list, string filePath, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\RemindList.xlsx";

            //System.Windows.Forms.MessageBox.Show(fileTemplate);

            //object fileOld = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\RemindList.xlsx";
            //System.Windows.Forms.MessageBox.Show(fileOld.ToString());


            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {


                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 2;//start from 2
                foreach (CarSvc.RemindListInfo info in list)
                {
                    excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = info.CarCode;
                    excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = info.RemindName;
                    excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = info.StartDate;
                    excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = info.EndDate;
                    excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = info.Next2KM;
                    excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = info.Remarks;
                    excelRow++;
                }

                excelWorkbook.SaveCopyAs(filePath);

                msg = "OK";
            }
            catch (Exception ex)
            {
                //System.Windows.Forms.MessageBox.Show(ex.StackTrace);
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;
        }

        /// <summary>
        /// 打印提醒清单
        /// </summary>
        /// <param name="list"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool RemindListPrint(CarSvc.RemindListInfo[] list, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\RemindList.xlsx";
            //string fileTemp = Environment.CurrentDirectory + @"\DocumentsTemp\" + getNewFileName() + ".xlsx";
            //System.Windows.Forms.MessageBox.Show(fileTemplate);

            //object fileOld = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\RemindList.xlsx";
            //System.Windows.Forms.MessageBox.Show(fileOld.ToString());

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 2;//start from 2
                foreach (CarSvc.RemindListInfo info in list)
                //for (int i = 0; i < list.Length; i++)
                {
                    excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = info.CarCode;
                    excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = info.RemindName;
                    excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = info.StartDate;
                    excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = info.EndDate;
                    excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = info.Next2KM;
                    excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = info.Remarks;
                    excelRow++;
                }

                //excelWorkbook.SaveCopyAs(fileTemp);
                excelWorksheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
                excelWorksheet.PrintOut(oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

                msg = "OK";
            }
            catch (Exception ex)
            {
                //System.Windows.Forms.MessageBox.Show(ex.StackTrace);
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;
        }

        private string getNewFileName()
        {
            return Login.LogTrueName + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();
        }

        /// <summary>
        /// 订单管理--短租带驾
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool OrderShortWithDriverListExport(DataSet dsCars, string filePath, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\Temp001.xlsx";

            //System.Windows.Forms.MessageBox.Show(fileTemplate);
            //object fileOld = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\OrderWithDriverList.xlsx";
            //System.Windows.Forms.MessageBox.Show(fileOld.ToString());

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 2;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DriverName"].ToString();
                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToBoolean(drCar["State"].ToString().Trim()) == true ? "租赁中" : "空闲中";
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Cartype"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Max"].ToString();
                        string strWarrTime = "";
                        if (drCar["WarrTime"].ToString().Trim() != "")
                        {
                            strWarrTime = Convert.ToDateTime(drCar["WarrTime"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = strWarrTime;
                        string strInsureTime = "";
                        if (drCar["InsureEnd"].ToString().Trim() != "")
                        {
                            strInsureTime = Convert.ToDateTime(drCar["InsureEnd"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = strInsureTime;
                        string strBuyTime = "";
                        if (drCar["BuyTime"].ToString().Trim() != "")
                        {
                            strBuyTime = Convert.ToDateTime(drCar["BuyTime"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = strBuyTime;
                        //string strCarTime = "";
                        //if (drCar["CarTime"].ToString().Trim() != "")
                        //{
                        //    strCarTime = Convert.ToDateTime(drCar["CarTime"].ToString().Trim()).ToString("yyyy-MM-dd");
                        //    if (strCarTime == "1990-01-01")
                        //        strCarTime = "";
                        //}
                        //excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = strCarTime;

                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DayRent"].ToString();
                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Color"].ToString();
                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["MotorCode"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["dipanCode"].ToString();
                        string strCareTime = "";
                        if (drCar["CareTime"].ToString().Trim() != "")
                        {
                            strCareTime = Convert.ToDateTime(drCar["CareTime"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = strCareTime;
                        string strStarCheshui = "";
                        if (drCar["StarCheshui"].ToString().Trim() != "")
                        {
                            strStarCheshui = Convert.ToDateTime(drCar["StarCheshui"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = strStarCheshui;
                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OvertimePrie"].ToString();
                        excelWorksheet.get_Range("Q" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DayLimitKM"].ToString();
                        excelWorksheet.get_Range("R" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TimeoutPrie"].ToString();

                        excelWorksheet.get_Range("S" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ExhaustVolume"].ToString();
                        excelWorksheet.get_Range("T" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["AirportShuttleOneWay"].ToString();
                        excelWorksheet.get_Range("U" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["AirportShuttleTwoWay"].ToString();
                        excelWorksheet.get_Range("V" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Chartered3Rent"].ToString();
                        excelWorksheet.get_Range("W" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OutdatedFee3"].ToString();
                        excelWorksheet.get_Range("X" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Chartered8Rent"].ToString();
                        excelWorksheet.get_Range("Y" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OutdatedFee8"].ToString();
                        excelWorksheet.get_Range("Z" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["LongJourneys1"].ToString();
                        excelWorksheet.get_Range("AA" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["LongJourneys2"].ToString();
                        excelWorksheet.get_Range("AB" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["LongJourneys3"].ToString();
                        excelRow++;
                    }
                }

                excelWorkbook.SaveCopyAs(filePath);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;
        }

        /// <summary>
        /// 订单管理--长租带驾
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool OrderWithDriverListExport(DataSet dsCars, string filePath, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\OrderWithDriverList.xlsx";

            //System.Windows.Forms.MessageBox.Show(fileTemplate);
            //object fileOld = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\OrderWithDriverList.xlsx";
            //System.Windows.Forms.MessageBox.Show(fileOld.ToString());

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 2;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DriverName"].ToString();
                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToBoolean(drCar["State"].ToString().Trim()) == true ? "租赁中" : "空闲中";
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Cartype"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Max"].ToString();
                        string strWarrTime = "";
                        if (drCar["WarrTime"].ToString().Trim() != "")
                        {
                            strWarrTime = Convert.ToDateTime(drCar["WarrTime"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = strWarrTime;
                        string strInsureTime = "";
                        if (drCar["InsureEnd"].ToString().Trim() != "")
                        {
                            strInsureTime = Convert.ToDateTime(drCar["InsureEnd"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = strInsureTime;
                        string strBuyTime = "";
                        if (drCar["BuyTime"].ToString().Trim() != "")
                        {
                            strBuyTime = Convert.ToDateTime(drCar["BuyTime"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = strBuyTime;
                        //string strCarTime = "";
                        //if (drCar["CarTime"].ToString().Trim() != "")
                        //{
                        //    strCarTime = Convert.ToDateTime(drCar["CarTime"].ToString().Trim()).ToString("yyyy-MM-dd");
                        //    if (strCarTime == "1990-01-01")
                        //        strCarTime = "";
                        //}
                        //excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = strCarTime;

                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DayRent"].ToString();
                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Color"].ToString();
                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["MotorCode"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["dipanCode"].ToString();
                        string strCareTime = "";
                        if (drCar["CareTime"].ToString().Trim() != "")
                        {
                            strCareTime = Convert.ToDateTime(drCar["CareTime"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = strCareTime;
                        string strStarCheshui = "";
                        if (drCar["StarCheshui"].ToString().Trim() != "")
                        {
                            strStarCheshui = Convert.ToDateTime(drCar["StarCheshui"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = strStarCheshui;
                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OvertimePrie"].ToString();
                        excelWorksheet.get_Range("Q" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DayLimitKM"].ToString();
                        excelWorksheet.get_Range("R" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TimeoutPrie"].ToString();
                        excelWorksheet.get_Range("S" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ExhaustVolume"].ToString();
                        excelRow++;
                    }
                }

                excelWorkbook.SaveCopyAs(filePath);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;
        }

        /// <summary>
        /// 订单管理--自驾
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool OrderWithOutDriverListExport(DataSet dsCars, string filePath, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\OrderWithOutDriverList.xlsx";

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 2;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        //excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DriverName"].ToString();
                        excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToBoolean(drCar["State"].ToString().Trim()) == true ? "租赁中" : "空闲中";
                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Cartype"].ToString();
                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Max"].ToString();
                        string strWarrTime = "";
                        if (drCar["WarrTime"].ToString().Trim() != "")
                        {
                            strWarrTime = Convert.ToDateTime(drCar["WarrTime"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = strWarrTime;
                        string strInsureTime = "";
                        if (drCar["InsureEnd"].ToString().Trim() != "")
                        {
                            strInsureTime = Convert.ToDateTime(drCar["InsureEnd"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = strInsureTime;
                        string strBuyTime = "";
                        if (drCar["BuyTime"].ToString().Trim() != "")
                        {
                            strBuyTime = Convert.ToDateTime(drCar["BuyTime"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = strBuyTime;
                        //string strCarTime = "";
                        //if (drCar["CarTime"].ToString().Trim() != "")
                        //{
                        //    strCarTime = Convert.ToDateTime(drCar["CarTime"].ToString().Trim()).ToString("yyyy-MM-dd");
                        //    if (strCarTime == "1990-01-01")
                        //        strCarTime = "";
                        //}
                        //excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = strCarTime;

                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DayRent"].ToString();
                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Color"].ToString();
                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["MotorCode"].ToString();
                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["dipanCode"].ToString();
                        string strCareTime = "";
                        if (drCar["CareTime"].ToString().Trim() != "")
                        {
                            strCareTime = Convert.ToDateTime(drCar["CareTime"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = strCareTime;
                        string strStarCheshui = "";
                        if (drCar["StarCheshui"].ToString().Trim() != "")
                        {
                            strStarCheshui = Convert.ToDateTime(drCar["StarCheshui"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = strStarCheshui;
                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OvertimePrie"].ToString();
                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DayLimitKM"].ToString();
                        excelWorksheet.get_Range("Q" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TimeoutPrie"].ToString();
                        excelWorksheet.get_Range("R" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ExhaustVolume"].ToString();
                        excelRow++;
                    }
                }

                excelWorkbook.SaveCopyAs(filePath);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;
        }

        /// <summary>
        /// 订单管理--所有订单
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool AllOrderListExport(DataSet dsCars, string filePath, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\AllOrderList.xlsx";

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 2;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        string strInpuTime = "";
                        if (drCar["InpuTime"].ToString().Trim() != "")
                        {
                            strInpuTime = Convert.ToDateTime(drCar["InpuTime"].ToString().Trim()).ToString("yyyy-MM-dd HH:mm:ss");
                        }
                        excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = strInpuTime;

                        excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["CustomerCode"].ToString();
                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Name"].ToString();
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Cartype"].ToString();

                        string strBooktime = "";
                        if (drCar["Booktime"].ToString().Trim() != "")
                        {
                            strBooktime = Convert.ToDateTime(drCar["Booktime"].ToString().Trim()).ToString("yyyy-MM-dd HH:mm:ss");
                        }
                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = strBooktime;

                        string strEndBooktime = "";
                        if (drCar["EndBooktime"].ToString().Trim() != "")
                        {
                            strEndBooktime = Convert.ToDateTime(drCar["EndBooktime"].ToString().Trim()).ToString("yyyy-MM-dd HH:mm:ss");
                        }
                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = strEndBooktime;

                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DayRent"].ToString();
                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Color"].ToString();

                        string strInsureEnd = "";
                        if (drCar["InsureEnd"].ToString().Trim() != "")
                        {
                            strInsureEnd = Convert.ToDateTime(drCar["InsureEnd"].ToString().Trim()).ToString("yyyy-MM-dd");
                        }
                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = strInsureEnd;

                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OvertimePrie"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DayLimitKM"].ToString();
                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TimeoutPrie"].ToString();
                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ExhaustVolume"].ToString();
                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ClassName"].ToString();
                        excelWorksheet.get_Range("Q" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["inputUserName"].ToString();
                        excelRow++;
                    }
                }

                excelWorkbook.SaveCopyAs(filePath);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;
        }

        /// <summary>
        /// 垃圾回收
        /// </summary>
        public void GCCollect()
        {
            System.GC.Collect();
        }

        /// <summary>
        /// 财务管理--按车辆统计导出
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool StatisticsByCarListExport(DataSet dsCars, string filePath, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\StatisticsByCarList.xlsx";

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 2;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        if (drCar["InpuTime"].ToString().Trim() != "")
                        {
                            excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["InpuTime"]).ToString("yyyy-MM-dd HH:mm:ss");
                        }
                        excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Owners"].ToString();
                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["Booktime"]).ToString("yyyy-MM-dd HH:mm:ss");
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["EndBooktime"]).ToString("yyyy-MM-dd HH:mm:ss");

                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["customerName"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["carType"].ToString();
                        string type = string.Empty;

                        if (Convert.ToInt32(drCar["Type"].ToString()) == 1)
                        {
                            type = "长租自驾";
                        }

                        else if (Convert.ToInt32(drCar["Type"].ToString()) == 2)
                        {
                            type = "短租自驾";
                        }
                        else if (Convert.ToInt32(drCar["Type"].ToString()) == 3)
                        {
                            type = "长租带驾";
                        }
                        else if (Convert.ToInt32(drCar["Type"].ToString()) == 4)
                        {
                            type = "短租带驾";
                        }
                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = type;
                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DayRent"].ToString();
                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["PayType"].ToString();

                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["yingshou"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ReceivedAmount"].ToString();
                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TotalAmount"].ToString();

                        decimal commissionRatio = 0;
                        decimal dayRent = 0;
                        decimal sumCommission = 0;
                        if (!string.IsNullOrEmpty(drCar["CommissionRatio"].ToString()))
                            commissionRatio = Convert.ToDecimal(drCar["CommissionRatio"].ToString()) / 100;

                        dayRent = Convert.ToDecimal(drCar["DayRent"].ToString());
                        sumCommission = Decimal.Round(commissionRatio * dayRent, 2);

                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = sumCommission;
                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["CommissionRatio"].ToString().Trim() == "" ? "0" : drCar["CommissionRatio"].ToString();
                        excelWorksheet.get_Range("Q" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["inputUserName"].ToString();

                        excelRow++;
                    }
                }
                excelWorkbook.SaveCopyAs(filePath);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;
        }

        /// <summary>
        /// 财务管理--按驾驶员统计 导出
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool StatisticsByDriverListExport(DataSet dsCars, string filePath, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\StatisticsByDriverList.xlsx";

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 2;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        if (drCar["InpuTime"].ToString().Trim() != "")
                        {
                            excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["InpuTime"]).ToString("yyyy-MM-dd");
                            excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["InpuTime"]).Hour + ":" + Convert.ToDateTime(drCar["InpuTime"]).Minute).ToString();
                        }

                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["Booktime"]).ToString("yyyy-MM-dd HH:mm:ss");
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["EndBooktime"]).ToString("yyyy-MM-dd HH:mm:ss");

                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DriverName"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["customerName"].ToString();
                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["carType"].ToString();

                        string pricetype = drCar["PriceType1"].ToString() + " - " + drCar["PriceType2"].ToString();
                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = pricetype;

                        string type = string.Empty;

                        if (Convert.ToInt32(drCar["Type"].ToString()) == 1)
                        {
                            type = "长租自驾";
                        }

                        else if (Convert.ToInt32(drCar["Type"].ToString()) == 2)
                        {
                            type = "短租自驾";
                        }
                        else if (Convert.ToInt32(drCar["Type"].ToString()) == 3)
                        {
                            type = "长租带驾";
                        }
                        else if (Convert.ToInt32(drCar["Type"].ToString()) == 4)
                        {
                            type = "短租带驾";
                        }

                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = type;
                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["LivAddr"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["AriAddr"].ToString();

                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Rent"].ToString();
                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["PayType"].ToString();

                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["yingshou"].ToString();
                        excelWorksheet.get_Range("Q" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TotalAmount"].ToString();

                        excelWorksheet.get_Range("R" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TimeoutPrie"].ToString();
                        excelWorksheet.get_Range("S" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OvertimePrie"].ToString();
                        excelWorksheet.get_Range("T" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["inputUserName"].ToString();

                        excelRow++;
                    }
                }
                excelWorkbook.SaveCopyAs(filePath);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;
        }

        /// <summary>
        /// 财务管理--按客户统计--短租带驾 导出
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool StatisticsByCustomerShortDriverExport(DataSet dsCars, string filePath, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\StatisticsByCustomerShortDriverList.xlsx";

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 3;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        if (drCar["InpuTime"].ToString().Trim() != "")
                        {
                            excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["InpuTime"]).ToString("yyyy-MM-dd");
                            excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["InpuTime"]).Hour + ":" + Convert.ToDateTime(drCar["InpuTime"]).Minute).ToString();
                        }

                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DriverName"].ToString();
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["customerName"].ToString();
                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["carType"].ToString();

                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Rent"].ToString();
                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["PayType"].ToString();
                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["LivAddr"].ToString();
                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["AriAddr"].ToString();

                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["yingshou"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ReceivedAmount"].ToString();
                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TotalAmount"].ToString();

                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TimeoutPrie"].ToString();
                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OvertimePrie"].ToString();
                        excelWorksheet.get_Range("Q" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["inputUserName"].ToString();

                        excelRow++;
                    }
                }
                excelWorkbook.SaveCopyAs(filePath);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;
        }

        /// <summary>
        /// 财务管理--按客户统计--长租带驾 导出
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool StatisticsByCustomerLongDriverExport(DataSet dsCars, string filePath, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\StatisticsByCustomerLongDriverList.xlsx";

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 3;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        if (drCar["InpuTime"].ToString().Trim() != "")
                        {
                            excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["InpuTime"]).ToString("yyyy-MM-dd");
                            excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["InpuTime"]).Hour + ":" + Convert.ToDateTime(drCar["InpuTime"]).Minute).ToString();
                        }

                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DriverName"].ToString();
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["customerName"].ToString();
                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["carType"].ToString();

                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Rent"].ToString();
                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["PayType"].ToString();
                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["EndBooktime"]).Date).ToString("yyyy-MM-dd");
                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["EndBooktime"]).Date).ToString("hh:mm:ss");

                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["LivAddr"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["AriAddr"].ToString();

                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["yingshou"].ToString();
                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ReceivedAmount"].ToString();
                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TotalAmount"].ToString();

                        excelWorksheet.get_Range("Q" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TimeoutPrie"].ToString();
                        excelWorksheet.get_Range("R" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OvertimePrie"].ToString();
                        excelWorksheet.get_Range("S" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["inputUserName"].ToString();

                        excelRow++;
                    }
                }
                excelWorkbook.SaveCopyAs(filePath);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;
        }

        /// <summary>
        /// 财务管理--按客户统计--短租自驾 导出
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool StatisticsByCustomerShortNotDriverExport(DataSet dsCars, string filePath, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\StatisticsByCustomerShortNotDriverList.xlsx";

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 3;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        if (drCar["InpuTime"].ToString().Trim() != "")
                        {
                            excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["InpuTime"]).ToString("yyyy-MM-dd");
                            excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["InpuTime"]).Hour + ":" + Convert.ToDateTime(drCar["InpuTime"]).Minute).ToString();
                        }

                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["customerName"].ToString();
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["carType"].ToString();

                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Rent"].ToString();
                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["PayType"].ToString();
                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["EndBooktime"]).Date).ToString("yyyy-MM-dd");
                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["EndBooktime"]).Date).ToString("hh:mm:ss");

                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["yingshou"].ToString();
                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ReceivedAmount"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TotalAmount"].ToString();

                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TimeoutPrie"].ToString();
                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OvertimePrie"].ToString();
                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["inputUserName"].ToString();


                        excelRow++;
                    }
                }
                excelWorkbook.SaveCopyAs(filePath);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;
        }

        /// <summary>
        /// 财务管理--按客户统计--长租自驾 导出
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool StatisticsByCustomerLongNotDriverExport(DataSet dsCars, string filePath, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\StatisticsByCustomerLongNotDriverList.xlsx";

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 3;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        if (drCar["InpuTime"].ToString().Trim() != "")
                        {
                            excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["InpuTime"]).ToString("yyyy-MM-dd");
                            excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["InpuTime"]).Hour + ":" + Convert.ToDateTime(drCar["InpuTime"]).Minute).ToString();
                        }

                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["customerName"].ToString();
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["carType"].ToString();

                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Rent"].ToString();
                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["PayType"].ToString();
                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["EndBooktime"]).Date).ToString("yyyy-MM-dd");
                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["EndBooktime"]).Date).ToString("hh:mm:ss");

                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["yingshou"].ToString();
                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ReceivedAmount"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TotalAmount"].ToString();

                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TimeoutPrie"].ToString();
                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OvertimePrie"].ToString();
                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["inputUserName"].ToString();


                        excelRow++;
                    }
                }
                excelWorkbook.SaveCopyAs(filePath);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;
        }


        /// <summary>
        /// 财务管理--按车辆统计 打印
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool PrintStatisticsByCarListExport(DataSet dsCars, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\StatisticsByCarList.xlsx";

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 2;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        if (drCar["InpuTime"].ToString().Trim() != "")
                        {
                            excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["InpuTime"]).ToString("yyyy-MM-dd HH:mm:ss");
                        }
                        excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Owners"].ToString();
                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["Booktime"]).ToString("yyyy-MM-dd HH:mm:ss");
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["EndBooktime"]).ToString("yyyy-MM-dd HH:mm:ss");

                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["customerName"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["carType"].ToString();
                        string type = string.Empty;

                        if (Convert.ToInt32(drCar["Type"].ToString()) == 1)
                        {
                            type = "长租自驾";
                        }

                        else if (Convert.ToInt32(drCar["Type"].ToString()) == 2)
                        {
                            type = "短租自驾";
                        }
                        else if (Convert.ToInt32(drCar["Type"].ToString()) == 3)
                        {
                            type = "长租带驾";
                        }
                        else if (Convert.ToInt32(drCar["Type"].ToString()) == 4)
                        {
                            type = "短租带驾";
                        }
                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = type;
                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DayRent"].ToString();
                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["PayType"].ToString();

                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["yingshou"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ReceivedAmount"].ToString();
                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TotalAmount"].ToString();

                        decimal commissionRatio = 0;
                        decimal dayRent = 0;
                        decimal sumCommission = 0;
                        if (!string.IsNullOrEmpty(drCar["CommissionRatio"].ToString()))
                            commissionRatio = Convert.ToDecimal(drCar["CommissionRatio"].ToString()) / 100;

                        dayRent = Convert.ToDecimal(drCar["DayRent"].ToString());
                        sumCommission = Decimal.Round(commissionRatio * dayRent, 2);

                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = sumCommission;
                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["CommissionRatio"].ToString().Trim() == "" ? "0" : drCar["CommissionRatio"].ToString();
                        excelWorksheet.get_Range("Q" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["inputUserName"].ToString();

                        excelRow++;
                    }
                }
                BeginPrint(excelWorksheet);
                //File.Delete(filePath.ToString());
                //excelWorkbook.SaveCopyAs(filePath);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;

        }

        /// <summary>
        /// 财务管理--按驾驶员统计 打印
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool PrintStatisticsByDriverList(DataSet dsCars, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\StatisticsByDriverList.xlsx";

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 2;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        if (drCar["InpuTime"].ToString().Trim() != "")
                        {
                            excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["InpuTime"]).ToString("yyyy-MM-dd");
                            excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["InpuTime"]).Hour + ":" + Convert.ToDateTime(drCar["InpuTime"]).Minute).ToString();
                        }

                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["Booktime"]).ToString("yyyy-MM-dd HH:mm:ss");
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = Convert.ToDateTime(drCar["EndBooktime"]).ToString("yyyy-MM-dd HH:mm:ss");

                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DriverName"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["customerName"].ToString();
                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["carType"].ToString();

                        string pricetype = drCar["PriceType1"].ToString() + " - " + drCar["PriceType2"].ToString();
                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = pricetype;

                        string type = string.Empty;

                        if (Convert.ToInt32(drCar["Type"].ToString()) == 1)
                        {
                            type = "长租自驾";
                        }

                        else if (Convert.ToInt32(drCar["Type"].ToString()) == 2)
                        {
                            type = "短租自驾";
                        }
                        else if (Convert.ToInt32(drCar["Type"].ToString()) == 3)
                        {
                            type = "长租带驾";
                        }
                        else if (Convert.ToInt32(drCar["Type"].ToString()) == 4)
                        {
                            type = "短租带驾";
                        }

                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = type;
                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["LivAddr"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["AriAddr"].ToString();

                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Rent"].ToString();
                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["PayType"].ToString();

                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["yingshou"].ToString();
                        excelWorksheet.get_Range("Q" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TotalAmount"].ToString();

                        excelWorksheet.get_Range("R" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TimeoutPrie"].ToString();
                        excelWorksheet.get_Range("S" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OvertimePrie"].ToString();
                        excelWorksheet.get_Range("T" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["inputUserName"].ToString();

                        excelRow++;
                    }
                }
                BeginPrint(excelWorksheet);
                //File.Delete(filePath.ToString());
                //excelWorkbook.SaveCopyAs(filePath);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;

        }

        /// <summary>
        /// 财务管理--按客户统计--短租带驾 打印
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool PrintStatisticsByCustomerShortDriverList(DataSet dsCars, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\StatisticsByCustomerShortDriverList.xlsx";

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 3;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        if (drCar["InpuTime"].ToString().Trim() != "")
                        {
                            excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["InpuTime"]).Date).ToString("yyyy-MM-dd");
                            excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["InpuTime"]).Hour + ":" + Convert.ToDateTime(drCar["InpuTime"]).Minute).ToString();

                        }
                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DriverName"].ToString();
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["customerName"].ToString();
                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["carType"].ToString();

                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Rent"].ToString();
                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["PayType"].ToString();
                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["LivAddr"].ToString();
                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["AriAddr"].ToString();

                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["yingshou"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ReceivedAmount"].ToString();
                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TotalAmount"].ToString();

                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TimeoutPrie"].ToString();
                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OvertimePrie"].ToString();
                        excelWorksheet.get_Range("Q" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["inputUserName"].ToString();

                        excelRow++;
                    }
                }
                BeginPrint(excelWorksheet);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;

        }

        /// <summary>
        /// 财务管理--按客户统计--长租带驾 打印
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool PrintStatisticsByCustomerLongDriverList(DataSet dsCars, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\StatisticsByCustomerLongDriverList.xlsx";

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 3;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        if (drCar["InpuTime"].ToString().Trim() != "")
                        {
                            excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["InpuTime"]).Date).ToString("yyyy-MM-dd");
                            excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["InpuTime"]).Hour + ":" + Convert.ToDateTime(drCar["InpuTime"]).Minute).ToString();

                        }
                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["DriverName"].ToString();
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["customerName"].ToString();
                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["carType"].ToString();

                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Rent"].ToString();
                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["PayType"].ToString();
                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["EndBooktime"]).Date).ToString("yyyy-MM-dd");
                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["EndBooktime"]).Date).ToString("hh:mm:ss");

                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["LivAddr"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["AriAddr"].ToString();

                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["yingshou"].ToString();
                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ReceivedAmount"].ToString();
                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TotalAmount"].ToString();

                        excelWorksheet.get_Range("Q" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TimeoutPrie"].ToString();
                        excelWorksheet.get_Range("R" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OvertimePrie"].ToString();
                        excelWorksheet.get_Range("S" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["inputUserName"].ToString();

                        excelRow++;
                    }
                }
                BeginPrint(excelWorksheet);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;

        }

        /// <summary>
        /// 财务管理--按客户统计--短租自驾 打印
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool PrintStatisticsByCustomerShortNotDriverList(DataSet dsCars, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\StatisticsByCustomerShortNotDriverList.xlsx";

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 3;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        if (drCar["InpuTime"].ToString().Trim() != "")
                        {
                            excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["InpuTime"]).Date).ToString("yyyy-MM-dd");
                            excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["InpuTime"]).Hour + ":" + Convert.ToDateTime(drCar["InpuTime"]).Minute).ToString();

                        }
                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["customerName"].ToString();
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["carType"].ToString();

                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Rent"].ToString();
                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["PayType"].ToString();
                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["EndBooktime"]).Date).ToString("yyyy-MM-dd");
                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["EndBooktime"]).Date).ToString("hh:mm:ss");

                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["yingshou"].ToString();
                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ReceivedAmount"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TotalAmount"].ToString();

                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TimeoutPrie"].ToString();
                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OvertimePrie"].ToString();
                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["inputUserName"].ToString();


                        excelRow++;
                    }
                }
                BeginPrint(excelWorksheet);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;

        }

        /// <summary>
        /// 财务管理--按客户统计--长租自驾 打印
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filePath"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool PrintStatisticsByCustomerLongNotDriverList(DataSet dsCars, ref string msg)
        {
            string fileTemplate = Environment.CurrentDirectory + @"\PrintExport\ExcelTemplates\StatisticsByCustomerLongNotDriverList.xlsx";

            Excel.Application excelApp = null;
            Excel.Workbook excelWorkbook = null;
            Excel.Worksheet excelWorksheet = null;
            bool bSsuccess = true;

            try
            {
                excelApp = new Excel.Application();
                //open a workbook in Excel
                excelWorkbook = excelApp.Workbooks.Open(
                    fileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //get the first sheet
                excelWorksheet = (Excel.Worksheet)excelWorkbook.Worksheets[1]; //excelSheets[1];

                //write to excel file
                int excelRow = 3;//start from 2

                if (dsCars != null && dsCars.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow drCar in dsCars.Tables[0].Rows)
                    {
                        if (drCar["InpuTime"].ToString().Trim() != "")
                        {
                            excelWorksheet.get_Range("A" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["InpuTime"]).Date).ToString("yyyy-MM-dd");
                            excelWorksheet.get_Range("B" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["InpuTime"]).Hour + ":" + Convert.ToDateTime(drCar["InpuTime"]).Minute).ToString();

                        }
                        excelWorksheet.get_Range("C" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["customerName"].ToString();
                        excelWorksheet.get_Range("D" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Code"].ToString();
                        excelWorksheet.get_Range("E" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Brand"].ToString();
                        excelWorksheet.get_Range("F" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["carType"].ToString();

                        excelWorksheet.get_Range("G" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["Rent"].ToString();
                        excelWorksheet.get_Range("H" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["PayType"].ToString();
                        excelWorksheet.get_Range("I" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["EndBooktime"]).Date).ToString("yyyy-MM-dd");
                        excelWorksheet.get_Range("J" + excelRow.ToString(), System.Type.Missing).Value2 = (Convert.ToDateTime(drCar["EndBooktime"]).Date).ToString("hh:mm:ss");

                        excelWorksheet.get_Range("K" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["yingshou"].ToString();
                        excelWorksheet.get_Range("L" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["ReceivedAmount"].ToString();
                        excelWorksheet.get_Range("M" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TotalAmount"].ToString();

                        excelWorksheet.get_Range("N" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["TimeoutPrie"].ToString();
                        excelWorksheet.get_Range("O" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["OvertimePrie"].ToString();
                        excelWorksheet.get_Range("P" + excelRow.ToString(), System.Type.Missing).Value2 = drCar["inputUserName"].ToString();

                        excelRow++;
                    }
                }
                BeginPrint(excelWorksheet);

                msg = "OK";
            }
            catch (Exception ex)
            {
                bSsuccess = false;
                msg = ex.ToString();
            }
            finally
            {
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close(false, Type.Missing, Type.Missing);
                }

                if (excelApp != null)
                {
                    excelApp.Quit();
                }

                if (excelWorksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorksheet);
                    excelWorksheet = null;
                }
                if (excelWorkbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                    excelWorkbook = null;
                }
                if (excelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                    excelApp = null;
                }
            }
            //msg = "OK";
            return bSsuccess;

        }

        //打印
        private void BeginPrint(Microsoft.Office.Interop.Excel.Worksheet doc)
        {
            try
            {
                doc.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
                doc.PrintOut(oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
            }
            catch (Exception ex)
            {
                Common.PageCommon.Alert(ex.Message);
            }
            finally
            {
                if (doc != null)
                {

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(doc);
                    doc = null;
                }
            }

        }
    }
}
